#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
-- 每天/每月/每年线上线下以及新老学员的意向用户个数
insert into hive.edu_rpt.rpt_customer_cnt_day_month_year
select
    date_time,
    year_code,
    year_month,
    year_month_day,
    time_type,
    group_type,
    clue_state,
    origin_type,
    id_count
from hive.edu_dm.dm_customer_relationship
where group_type = 'all'
order by id_count desc
;


-- 每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
insert into hive.edu_rpt.rpt_area_customer_cnt_day_month_year
select
    date_time,
    year_code,
    year_month,
    year_month_day,
    time_type,
    group_type,
    area,
    clue_state,
    origin_type,
    id_count
from hive.edu_dm.dm_customer_relationship
where group_type = 'area'
order by id_count desc;


-- 每天/每月/每年各学科线上线下以及新老学员的意向用户个数  Top10
insert into hive.edu_rpt.rpt_subject_customer_cnt_day_month_year
select
    date_time,
    year_code,
    year_month,
    year_month_day,
    time_type,
    group_type,
    sub_id,
    sub_name,
    clue_state,
    origin_type,
    id_count
from hive.edu_dm.dm_customer_relationship
where group_type = 'subject'
order by id_count desc;


-- 每天/每月/每年各校区线上线下以及新老学员的意向用户个数  Top10
insert into hive.edu_rpt.rpt_school_customer_cnt_day_month_year
select
    date_time,
    year_code,
    year_month,
    year_month_day,
    time_type,
    group_type,
    school_id,
    school_name,
    clue_state,
    origin_type,
    id_count
from hive.edu_dm.dm_customer_relationship
where group_type = 'school'
order by id_count desc;


-- 每天/每月/每年各来源渠道线上线下以新老学员的意向用户个数
insert into hive.edu_rpt.rpt_origin_channel_customer_cnt_day_month_year
select
    date_time,
    year_code,
    year_month,
    year_month_day,
    time_type,
    group_type,
    origin_channel,
    clue_state,
    origin_type,
    id_count
from hive.edu_dm.dm_customer_relationship
where group_type = 'origin_channel'
order by id_count desc;


-- 每天/每月/每年各咨询中心线上线下以及新老学员的意向用户个数
insert into hive.edu_rpt.rpt_depart_customer_cnt_day_month_year
select
    date_time,
    year_code,
    year_month,
    year_month_day,
    time_type,
    group_type,
    depart_id,
    depart_name,
    clue_state,
    origin_type,
    id_count
from hive.edu_dm.dm_customer_relationship
where group_type = 'depart'
order by id_count desc;

---=====================================================================================

-- 每天线上线下及新老学员的有效线索个数
insert into hive.edu_rpt.rpt_effective_customer_cnt_day
select
    date_time,
    dt,
    group_type,
    clue_state,
    origin_type,
    appeal_status,
    first_id_effective_count
from hive.edu_dm.dm_customer_clue
where group_type = 'dt' and appeal_status = 2 and dt is not null
order by first_id_effective_count desc;


-- 每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数
insert into hive.edu_rpt.rpt_effective_ratio_customer_datetime
select
    date_time,
    datetime,
    group_type,
    clue_state,
    origin_type,
    appeal_status,
    first_id_effective_count,
    first_id_count,
    cast(cast(first_id_effective_count as decimal(38,4)) / if(cast(first_id_count as decimal(38,4))=0,null,cast(first_id_count as decimal(38,4))) * 100 as decimal(5,2)) as effective_count_ratio
from hive.edu_dm.dm_customer_clue
where group_type = 'time_hour' and datetime is not null;

"